﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DTO;
namespace DA
{
    // chi tiết báo
    public class ChiTietBaoDA : DBConnection
    {
        // hàm tạo
        public ChiTietBaoDA()
        {
            sqlCommand.Connection = sqlConnection;
        }
        // lấy tất cả
        public DataSet GetAll()
        {
            var ds = new DataSet();
            const string query = "select * from CHITIETBAO";
            if (open())
            {
                sqlDataAdapter = new SqlDataAdapter(query, sqlConnection);
                sqlDataAdapter.Fill(ds);

            }
            close();
            return ds;
        }
        // lấy giá báo
        public string GetGiaTien(DTO.ChiTietBaoDTO item)
        {
            var values = string.Empty;
            string query = String.Format("select GiaTienBao from CHITIETBAO where MaBao='{0}' and MaViTri='{1}' and MaMauSac='{2}'",
                              item.MaBao, item.MaViTri, item.MaMauSac);
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader == null)
                {
                    return string.Empty;
                }
                while (sqlDataReader.Read())
                {
                    values = sqlDataReader[0].ToString();
                }
                close();
                return values;
            }
            catch
            {
                return values;
            }
            finally
            {
                if (sqlDataReader != null)
                {
                    sqlDataReader.Close();
                }
                close();
            }
        }
        public int GetID(DTO.ChiTietBaoDTO item)
        {
            string query = String.Format("select MaChiTietBao from CHITIETBAO where MaViTri='{0}' and MaMauSac='{1}' and MaBao='{2}'", item.MaViTri, item.MaMauSac, item.MaBao);

            var id = 0;
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader == null)
                {
                    return 0;
                }
                while (sqlDataReader.Read())
                {
                    id = sqlDataReader.GetInt32(0);
                }
                close();
                return id;
            }
            catch
            {
                return 0;
            }
            finally
            {
                if (sqlDataReader != null)
                {
                    sqlDataReader.Close();
                }
                close();
            }
        }
        // thêm 
        public int Add(ChiTietBaoDTO item)
        {
            var query = String.Format("insert into CHITIETBAO values('{0}','{1}','{2}','{3}'); select SCOPE_IDENTITY();", item.MaViTri, item.MaMauSac, item.GiaTienBao, item.MaBao);
            sqlCommand.CommandText = query;
            try
            {
                open();
                var idRe = -1;
                sqlCommand.CommandText = query;
                var re = sqlCommand.ExecuteScalar().ToString();
                idRe = Int32.Parse(re);
                close();
                return idRe;
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                return -1;
            }
        }
        // sửa
        public bool Update(ChiTietBaoDTO item)
        {
            var query = String.Format("Update CHITIETBAO set MaViTri = '{0}',MaMauSac='{1}',GiaTienBao='{2}',MaBao='{3}' where MaChiTietBao = {4}",
                item.MaViTri, item.MaMauSac, item.GiaTienBao, item.MaBao, item.MaChiTietBao);
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlCommand.ExecuteNonQuery();
                sqlCommand.Parameters.Clear();
                close();
                return true;
            }
            catch (SqlException s)
            {
                Console.WriteLine(s.Message);
                return false;
            }
        }
        // xóa 
        public bool Delete(int ma_item)
        {
            var query = "Delete from CHITIETBAO where MaChiTietBao = " + ma_item;
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlCommand.ExecuteNonQuery();
                sqlCommand.Parameters.Clear();
                close();
                return true;
            }
            catch (SqlException s)
            {
                Console.WriteLine(s.Message);
                return false;
            }
        }
        public int getMax_MaChiTietBao()
        {
            const string _getMasoluong = "select MAX(MaChiTietBao) from CHITIETBAO";
            return new MyQueryDA().getId(_getMasoluong);
        }
    }
}
